package com.mobiqueen.data;

import com.mobiqueen.generic.ApplicationException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseAdapter {

	public static final String KEY_ROWID = "_id";
	public static final String KEY_NAME = "name";
	public static final String KEY_ARTIST1 = "artist_1";
	public static final String KEY_ARTIST2 = "artist_2";
	public static final String KEY_ARTIST3 = "artist_3";
	public static final String KEY_LINK_ARTIST1 = "link_artist_1";
	public static final String KEY_LINK_ARTIST2 = "link_artist_2";
	public static final String KEY_LINK_ARTIST3 = "link_artist_3";
	public static final String KEY_GENRE = "genre";
	public static final String KEY_BEGIN_TIME = "begin_time";
	public static final String KEY_END_TIME = "end_time";
	public static final String KEY_PODIUM = "podium";

	public static final String KEY_LATITUDE = "latitude";
	public static final String KEY_LONGITUDE = "longitude";
	public static final String KEY_STREET = "street";
	public static final String KEY_HOUSENR = "housenr";
	public static final String KEY_ZIPCODE = "zipcode";
	public static final String KEY_CITY = "city";

	private static final String TAG = "DatabaseAdapter";

	private static final String DATABASE_NAME = "mobiqueendb";
	private static final String DATABASE_PERFORMANCE_TABLE = "performance";
	private static final String DATABASE_PODIUM_TABLE = "podium";
	private static final int DATABASE_VERSION = 6;

	private static final String DATABASE_CREATE = "CREATE TABLE podium (_id integer primary key autoincrement, "
			+ "name text not null, "
			+ "latitude real, "
			+ "longitude real, "
			+ "street text, "
			+ "housenr text, "
			+ "zipcode text, "
			+ "city text);";

	private static final String DATABASE_CREATE1 = "CREATE TABLE performance (_id integer primary key autoincrement, "
			+ "name text not null, "
			+ "artist_1 text not null, "
			+ "artist_2 text, "
			+ "artist_3 text, "
			+ "link_artist_1 text, "
			+ "link_artist_2 text, "
			+ "link_artist_3 text, "
			+ "begin_time date not null, "
			+ "end_time date not null, "
			+ "genre text not null,"
			+ "podium integer, "
			+ "FOREIGN KEY (podium) REFERENCES podium(_id));";

	private final Context context;

	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public DatabaseAdapter(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.execSQL(DATABASE_CREATE);
				db.execSQL(DATABASE_CREATE1);
			} catch (SQLiteException e) {
				Log.e("createerr", e.toString());
			}
		}

		@Override
		public	void
				onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_PODIUM_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_PERFORMANCE_TABLE);
			onCreate(db);
		}
	}

	// ---opens the database---
	public DatabaseAdapter open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}

	// ---closes the database---
	public void close() {
		DBHelper.close();
	}

	/* podium */
	
	/* insert new podium */
	public long insertPodium(	String name,
								double lat,
								double lon,
								String street,
								String housenr,
								String zipcode,
								String city) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NAME, name);
		initialValues.put(KEY_LATITUDE, lat);
		initialValues.put(KEY_LONGITUDE, lon);
		initialValues.put(KEY_STREET, street);
		initialValues.put(KEY_HOUSENR, housenr);
		initialValues.put(KEY_ZIPCODE, zipcode);
		initialValues.put(KEY_CITY, city);

		return db.insert(DATABASE_PODIUM_TABLE, null, initialValues);
	}

	/* delete podium with the given id */
	public boolean deletePodium(long rowId) throws ApplicationException {
		Cursor c = getPerformanceForPodium(rowId);
		c.moveToFirst();
		boolean performanceExistsForPodium = c.getCount() > 0?true:false;
		if (performanceExistsForPodium)
			throw new ApplicationException("Referential integrity fault. Delete not allowed.");
		
		return db.delete(DATABASE_PODIUM_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
		
			
	}

	/* delete podium with the given name */
	public boolean deletePodium(String name) throws ApplicationException {
		Cursor c = getPerformanceForPodium(name);
		c.moveToFirst();
		boolean performanceExistsForPodium = c.getCount() > 0?true:false;
		if (performanceExistsForPodium)
			throw new ApplicationException("Referential integrity fault. Delete not allowed.");
		
		return db.delete(DATABASE_PODIUM_TABLE, KEY_NAME + "='" + name + "'", null) > 0;
	}

	/* get all podiums */
	public Cursor getAllPodiums() {
		return db.query(DATABASE_PODIUM_TABLE, new String[] { KEY_ROWID,
															  KEY_NAME, 
															  KEY_LATITUDE, 
															  KEY_LONGITUDE, 
															  KEY_STREET, 
															  KEY_HOUSENR,
															  KEY_ZIPCODE, 
															  KEY_CITY, }, 
															  null, 
															  null, 
															  null, 
															  null, 
															  null);
	}

	/* get the podium with the given id */
	public Cursor getPodium(long rowId) throws SQLException {
		Cursor mCursor = db.query(true, DATABASE_PODIUM_TABLE, new String[] { KEY_ROWID, 
																			  KEY_NAME, 
																			  KEY_LATITUDE, 
																			  KEY_LONGITUDE, 
																			  KEY_STREET,
																			  KEY_HOUSENR, 
																			  KEY_ZIPCODE, 
																			  KEY_CITY, }, 
																			  KEY_ROWID + "=" + rowId,
																			  null, 
																			  null, 
																			  null, 
																			  null, 
																			  null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/* get the podium with the given name */
	public Cursor getPodium(String name) throws SQLException {
		Cursor mCursor = db.query(true, DATABASE_PODIUM_TABLE, new String[] { KEY_ROWID, 
																			  KEY_NAME, 
																			  KEY_LATITUDE, 
																			  KEY_LONGITUDE, 
																			  KEY_STREET,
																			  KEY_HOUSENR, 
																			  KEY_ZIPCODE, 
																			  KEY_CITY, }, 
																			  KEY_NAME + "=" + KEY_NAME,
																			  null, 
																			  null, 
																			  null, 
																			  null, 
																			  null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	/* update podium with the given id */
	public boolean updatePodium(long rowId,
								String name,
								double lat,
								double lon,
								String street,
								String housenr,
								String zipcode,
								String city) {
		ContentValues args = new ContentValues();
		args.put(KEY_NAME, name);
		args.put(KEY_LATITUDE, lat);
		args.put(KEY_LONGITUDE, lon);
		args.put(KEY_STREET, street);
		args.put(KEY_HOUSENR, housenr);
		args.put(KEY_ZIPCODE, zipcode);
		args.put(KEY_CITY, city);

		return db.update(DATABASE_PODIUM_TABLE, args, KEY_ROWID + "=" + rowId,
				null) > 0;
	}

	/* performance */
	// ---insert a podium into the database---
	public long insertPerformance(	String name,
									String artist1,
									String artist2,
									String artist3,
									String linkArtist1,
									String linkArtist2,
									String linkArtist3,
									String beginTime,
									String endTime,
									String genre,
									int podium) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NAME, name);
		initialValues.put(KEY_ARTIST1, artist1);
		initialValues.put(KEY_ARTIST2, artist2);
		initialValues.put(KEY_ARTIST3, artist3);
		initialValues.put(KEY_LINK_ARTIST1, linkArtist1);
		initialValues.put(KEY_LINK_ARTIST2, linkArtist2);
		initialValues.put(KEY_LINK_ARTIST3, linkArtist3);
		initialValues.put(KEY_BEGIN_TIME, beginTime);
		initialValues.put(KEY_END_TIME, endTime);
		initialValues.put(KEY_GENRE, genre);
		initialValues.put(KEY_PODIUM, podium);

		return db.insert(DATABASE_PERFORMANCE_TABLE, null, initialValues);
	}

	// ---deletes a particular podium---
	public boolean deletePerformance(long rowId) {
		return db.delete(DATABASE_PERFORMANCE_TABLE, KEY_ROWID + "=" + rowId,
				null) > 0;
	}

	// ---deletes a particular podium by name---
	public boolean deletePerformance(String name) {
		return db.delete(DATABASE_PERFORMANCE_TABLE, KEY_NAME + "='" + name
				+ "'", null) > 0;
	}

	// ---retrieves all the titles---
	public Cursor getAllPerformances() {
		return db.query(DATABASE_PERFORMANCE_TABLE, new String[] {  KEY_ROWID,
																	KEY_NAME, 
																	KEY_ARTIST1, 
																	KEY_ARTIST2, 
																	KEY_ARTIST3,
																	KEY_LINK_ARTIST1, 
																	KEY_LINK_ARTIST2, 
																	KEY_LINK_ARTIST3,																	 
																	KEY_BEGIN_TIME, 
																	KEY_END_TIME,
																	KEY_GENRE,
																	KEY_PODIUM }, 
																	null, 
																	null, 
																	null,
																	null, 
																	null);
	}
	
// -retrieves the performances of ane particular podium
	public Cursor getPerformanceForPodium(long podiumRowId) throws SQLException {
		Cursor mCursor = db.query(true, DATABASE_PERFORMANCE_TABLE,
				new String[] { KEY_ROWID, 
				               KEY_NAME, 
				               KEY_ARTIST1, 
				               KEY_ARTIST2,
				               KEY_ARTIST3, 
				               KEY_LINK_ARTIST1, 
				               KEY_LINK_ARTIST2,
				               KEY_LINK_ARTIST3, 				                
				               KEY_BEGIN_TIME,
				               KEY_END_TIME,
				               KEY_GENRE,
				               KEY_PODIUM}, KEY_PODIUM + "=" + podiumRowId, 
				               null, 
				               null,
				               null, 
				               null, 
				               null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor getPerformanceForPodium(String podiumName) throws SQLException {
		Cursor mCursor = db.query(true, DATABASE_PERFORMANCE_TABLE,
				new String[] { KEY_ROWID, 
				               KEY_NAME, 
				               KEY_ARTIST1, 
				               KEY_ARTIST2,
				               KEY_ARTIST3, 
				               KEY_LINK_ARTIST1, 
				               KEY_LINK_ARTIST2,
				               KEY_LINK_ARTIST3, 				                
				               KEY_BEGIN_TIME,
				               KEY_END_TIME,
				               KEY_GENRE,
				               KEY_PODIUM}, KEY_NAME + "=" + podiumName, 
				               null, 
				               null,
				               null, 
				               null, 
				               null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	// ---retrieves a particular title---
	public Cursor getPerformance(long rowId) throws SQLException {
		Cursor mCursor = db.query(true, DATABASE_PERFORMANCE_TABLE,
				new String[] { KEY_ROWID, 
				               KEY_NAME, 
				               KEY_ARTIST1, 
				               KEY_ARTIST2,
				               KEY_ARTIST3, 
				               KEY_LINK_ARTIST1, 
				               KEY_LINK_ARTIST2,
				               KEY_LINK_ARTIST3, 				                
				               KEY_BEGIN_TIME,
				               KEY_END_TIME,
				               KEY_GENRE,
				               KEY_PODIUM}, KEY_ROWID + "=" + rowId, 
				               null, 
				               null,
				               null, 
				               null, 
				               null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	// ---updates a title---
	public boolean updatePerformance(	long rowId,
										String name,
										String artist1,
										String artist2,
										String artist3,
										String linkArtist1,
										String linkArtist2,
										String linkArtist3,
										String beginTime,
										String endTime,
										String genre,
										int podium) {
		ContentValues args = new ContentValues();
		args.put(KEY_NAME, name);
		args.put(KEY_ARTIST1, artist1);
		args.put(KEY_ARTIST2, artist2);
		args.put(KEY_ARTIST3, artist3);
		args.put(KEY_LINK_ARTIST1, linkArtist1);
		args.put(KEY_LINK_ARTIST2, linkArtist2);
		args.put(KEY_LINK_ARTIST3, linkArtist3);
		args.put(KEY_BEGIN_TIME, beginTime);
		args.put(KEY_BEGIN_TIME, endTime);
		args.put(KEY_GENRE, genre);
		args.put(KEY_PODIUM, podium);
		return db.update(DATABASE_PERFORMANCE_TABLE, args, KEY_ROWID + "="
				+ rowId, null) > 0;
	}

}
